package jcu.ict.profaid;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "ProfAid";
	private static final int DATABASE_VERSION = 1;

	private static final String DATABASE_CREATE_TABLE_COURSE = "create table course ( course_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "user_id INTEGER NOT NULL,"
			+ "university varchar(100) not null, "
			+ "program varchar(100), "
			+ "course_code varchar(10) not null,"
			+ "course_name varchar(100) not null, "
			+ "date varchar(30) not null, " 
			+ "notes varchar(300),"
			+ "FOREIGN KEY(user_id) REFERENCES Sign(user_id) ON DELETE CASCADE);";
	

	private static final String DATABASE_CREATE_TABLE_QUESTION = "create table question ( question_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "user_id INTEGER NOT NULL,"
			+ "date varchar(20) not null, "
			+ "title varchar(50), "
			+ "question_time varchar(50),"
			+ "desc_question varchar(300), "
			+ "course_id INTEGER not null, "
			+ "answer_time INTEGER, "
			+ "FOREIGN KEY(course_id) REFERENCES course(course_id) ON DELETE CASCADE);";

	private static final String DATABASE_CREATE_TABLE_ANSWER = "create table answer ( answer_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "user_id INTEGER NOT NULL,"
			+ "answer_desc varchar(300) not null,"
			+ "answer_date_time varchar(70),"
			+ "question_id INTEGER not null,"
			+ "course_id INTEGER not null, "
			+ "answer_sum INTEGER not null DEFAULT 0,"
			+ "time_flag INTEGER DEFAULT 0,"
			+ "FOREIGN KEY(question_id) REFERENCES question(question_id) ON DELETE CASCADE);";

	private static final String DATABASE_CREATE_TABLE_SIGN = "create table Sign ( user_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "username varchar(300) not null,"
			+ "email varchar(70) not null,"
			+ "phone varchar(30) not null );";

	private static final String DATABASE_CREATE_TABLE_PHONE_NUMBER = "create table PhoneNumber (phone_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "
			+ "user_id INTEGER NOT NULL,"
			+ "PhoneNumber varchar(20) not null,"
			+ "question_id integer not null," 
			+ "CourseId integer not null," 
			+ "FOREIGN KEY(question_id) REFERENCES question(question_id) ON DELETE CASCADE);";

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(DATABASE_CREATE_TABLE_COURSE);
		db.execSQL(DATABASE_CREATE_TABLE_QUESTION);
		db.execSQL(DATABASE_CREATE_TABLE_ANSWER);
		db.execSQL(DATABASE_CREATE_TABLE_SIGN);
		db.execSQL(DATABASE_CREATE_TABLE_PHONE_NUMBER);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		Log.w("DBAdapter", "Upgrading database from version " + oldVersion
				+ " to " + newVersion + ", which will destroy all old data");
		db.execSQL("DROP TABLE IF EXISTS course");
		db.execSQL("DROP TABLE IF EXISTS question");
		db.execSQL("DROP TABLE IF EXISTS answer");
		onCreate(db);

	}

}
